SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		zcg
-- Create date: 2013.2.26
-- Description:	收付款凭证生成
-- =============================================
CREATE PROCEDURE [dbo].[sp_jiuqi_voucher_receipt_payment] 
@id int,@vchrtypecode varchar(100) output

AS
BEGIN	
	--=========查询中间数据==========
	declare @companyId int,--公司ID
			@customerId int,--客户ID
			@bankId int,--银行ID
			@inctype int,--5=银票,6=商票
			@type int,--11=供应付款,12=销售退款,21=销售收款,22=供应退款
			@stype int,--1=应收款,2=预收款,3=应付款,4=预付款
			@usetype int,--1=商品款，2=运费
			@sum numeric(18,2),
			@bizDate smalldatetime

	select @companyId=dbo.gf_sys_cwcompid_by_compid(compid),@customerId=custid,@bankId=isnull(bankid,0),
	@sum=je,@inctype=inctype,@stype=stype,@type=type,@usetype=usetype,@bizDate=dt
	from t_cw_sfk where sfkid=@id

	--=========判断公司和客户的类型==========
	declare @intercompany int,--是否是内部公司，1=是 0=否
			@headquarters int --是否是公司总部，1=是 0=否
	--判断公司是否是总部
	select @headquarters=isnull(iszb,0) from t_sys_comp where compid=@companyId
	--判断客户是否是内部公司
	if exists(select 1 from t_sys_comp where compid=@customerId and status=1)
		select @intercompany=1
	else
		select @intercompany=0
	--====================================================
	--===================凭证头部分=======================
	--====================================================
	
	select @vchrtypecode=(case when t.inctype in(5,6) then  '转帐' else '银行' end)
    from t_cw_sfk t,t_jiuqi_company c where t.compid=c.company_id and t.sfkid=@id
	--====================================================
	--=================凭证分录部分=======================
	--====================================================
	--金额
	set @sum=(case when @type in (11,21) then @sum else -@sum end)

	--客户名称
	declare @customerName varchar(100);
	if @intercompany=1 --内部转账
		select @customerName=dbo.gf_custid_fullname(@customerId)
	else if @type in (11,22) --11=供应付款,22=供应退款
		select @customerName=dbo.gf_custid_fullname(custid) from t_u8_vendor where compid=@companyId and custid=@customerId
	else if @type in (12,21) --12=销售退款,21=销售收款
		select @customerName=dbo.gf_custid_fullname(custid) from t_u8_customer where compid=@companyId and custid=@customerId


	--摘要
	declare @note varchar(255),
			@companyCode varchar(255),
			@companyName varchar(255)
	if @intercompany=1
		select @note=(case @type when 11 then '付'+@customerName
				  			 when 22 then '收'+@customerName
							 when 21 then '收'+@customerName
							 when 12 then '付'+@customerName end)+'经营款';
	else
		select @note=(case @type when 11 then '付'+@customerName
				  				 when 22 then @customerName+'退'
								 when 21 then '收'+@customerName
								 when 12 then '退'+@customerName end)
						+(case when @intercompany=1 then '经营款'
							else
								(case when @usetype=1 then (case @stype when 1 then '应收款'   when 2 then '预收款'   when 3 then '应付款'   when 4 then '预付款' end)
									  when @usetype=2 then (case @stype when 1 then '应收运费' when 2 then '预收运费' when 3 then '应付运费' when 4 then '预付运费' end) 
						 end)
				 end)

	--借方
    declare  @debtorCodeId int,@debtorCode varchar(100),@debtorName varchar(100),@isdeptId1 int
    --贷方
    declare @creditCodeId int,@creditCode varchar(100),@creditName varchar(100),@isdeptId2 int
    --客户,供应商
    declare @customerCode varchar(100),@supplierCode varchar(100)

--当客户不是内部机构,收付类型为=供应付款或=供应退款,预付款
    set @isdeptid1=0
    set @isdeptid2=0
	set @isdeptId1=(case when @type in (11,22) and @intercompany=0 and @stype=4 then 1 else 0 end)

	--8.销售环节(12=销售退款,21=销售收款)
	if @type in (21,12)
	begin
		--借方(科目代码和科目名称)
		set @debtorCode=(case when @inctype in (5,6) then
							(select code from t_jiuqi_common_subject_code where company_id=@companyId and code_id=(case @inctype when 5 then 600 else 601 end))
							else (select jiuqi_saving_code from t_cw_bank where bankid=@bankId) end)
		set @debtorName=(case when @inctype in (5,6) then
			                (select subject_name from t_jiuqi_common_subject where code_id=(case @inctype when 5 then 600 else 601 end))
						else '银行存款/'+(select name from t_cw_bank where bankid=@bankId)  end)
		--贷方(科目代码和科目名称)
		select @creditCode=(select code from t_jiuqi_common_subject_code where company_id=@companyId and code_id=(case @stype when 1 then 100 when 2 then 200 end))
		select @creditName=(select subject_name from t_jiuqi_common_subject where code_id=(case @stype when 1 then 100 when 2 then 200 end))+'/'+dbo.gf_custid_fullname(@customerId)

		--客户(代码)
		select @customerCode=(select code from t_jiuqi_customer_mapping where company_id=@companyId and customer_id=@customerId)
	end

	--9.供应环节(11=供应付款,22=供应退款)
	if @type in (11,22)
    begin
      --借方(科目代码和科目名称)
      select @debtorCodeId=(case when @stype=4 then 500 when @stype=3 then (case @usetype when 1 then 300 else 400 end) end)
      select @debtorCode =(select code from t_jiuqi_common_subject_code where company_id=@companyId and code_id=@debtorCodeId)
      select @debtorName=(select subject_name from t_jiuqi_common_subject where code_id=@debtorCodeId)+'/'+dbo.gf_custid_fullname(@customerId)

      declare @skid int,@init int
      select @skid=b.skid,@init=init from t_cw_sfk a,t_cw_pj b where sfkid=@id and (a.type=11 and a.sfkid=b.fkid or a.type=22 and a.sfkid=b.skid)
      select @creditCodeId=(case when @skid>0 or @init=1 then (case @inctype when 5 then 600 when 6 then 601 end) else 700 end)
      --select @isdeptid1=(case when @iszb=1 then 1 else @isdeptid1 end)

    --贷方(科目代码和科目名称)
      select @creditCode=(case when @inctype in (5,6) then
                                        (select code from t_jiuqi_common_subject_code where  company_id=@companyId  and code_id=@creditCodeId)
                                        else (select jiuqi_saving_code from t_cw_bank where bankid=@bankId)  end),
             @creditName=(case when @inctype in(5,6) then (select subject_name from t_jiuqi_common_subject where code_id=@creditCodeId)
                                      else '银行存款/'+(select name from t_cw_bank where bankid=@bankId)  end)
      --供应商
      select @supplierCode=(select code from t_jiuqi_supplier_mapping where company_id=@companyId and supplier_id=@customerId)
    end

    --内部转账
    if @intercompany=1
    begin
        select @supplierCode=null,@creditCodeId=null
        --销售环节
        if @type in (21,12)
            begin
                select @creditCode=(select code from t_jiuqi_company_mapping where company1_id=@companyId and company2_id=@customerId)
                select @creditName='其他应付款/'+dbo.gf_custid_fullname(@customerId)
                select @isdeptId2=0
            end
        else
            --供应环节
            begin
                select @debtorCode=(select code from t_jiuqi_company_mapping where company1_id=@companyId and company2_id=@customerId)
                select @debtorName='其他应付款/'+dbo.gf_custid_fullname(@customerId)
                select @isdeptId1=0
            end
    end
    --非内部经营款+非退款
    if @intercompany<>1 or @sum>0
    begin
        select
            note=@note,
            ccode=@debtorCode,
            cname=@debtorName,
            md=@sum,
            mc=0.00,
            ismd=1,
            ismc=0,
            customerCode=null,
            supplierCode=@supplierCode,
            isdept=@isdeptId1,
            isproc=(case when @type in (21,12) and @inctype not in (5,6) then 1 else 0 end),
            bizDate=@bizDate
        union all
        select
            note=@note,
            ccode=@creditCode,
            ccname=@creditName,
            md=0.00,
            mc=@sum,
            ismd=0,
            ismc=1,
            customerCode=@customerCode,
            supplierCode=null,
            isdept=@isdeptId2,
            isproc=(case when @type in (11,22) and @inctype not in (5,6) then 1 else 0 end),
            bizDate=@bizDate
    end
    else
    begin
        select
            note=@note,
            ccode=@creditCode,
            ccname=@creditName,
            md=-@sum,
            mc=0.00,
            ismd=1,
            ismc=0,
            customerCode=@customerCode,
            supplierCode=null,
            isdept=@isdeptId2,
            isproc=(case when @type in (11,22) and @inctype not in (5,6) then 1 else 0 end),
            bizDate=@bizDate
        union all
        select
            note=@note,
            ccode=@debtorCode,
            ccname=@debtorName,
            md=0.00,
            mc=-@sum,
            ismd=0,
            ismc=1,
            customerCode=null,
            supplierCode=@supplierCode,
            isdept=@isdeptId1,
            isproc=(case when @type in (21,12) and @inctype not in (5,6) then 1 else 0 end),
            bizDate=@bizDate
    end

END
GO
